Selecting Properties to Display in a Grid
Several controls contain a list of properties that can be selected manually. For example, in the User - Info Grid you can select details to display about the user that is currently logged in.
Select the grid control.
Click the Properties tab.
Enable items in the Properties pane that will be shown in the control. The items will be displayed to the user in the order they are added in.
Click OK.
The properties are displayed in the control.
Using a Query to Populate a Control
Several controls can be populated with data from the database. For example, this could be useful if you wanted to create a new unit and needed to select a color for it from a list of the existing colors.
Let's use the above example to populate a control.
Create a new Create Unit form. This contains several controls that can be populated.
Add the Generic Listbox control to the form.
Click the Properties tab. This will open up a list of options.
In the ATS Inspect category, select the Query row and click the ellipses 
button.
This opens the Query popup. The query is entered in the top pane. When entering the query you can use existing values and variables using the drop-down lists at the top of the popup as follows:
Add Control Value: Contains the output values from other controls in the form. This can be useful to filter the list.
Add Global Variable: Contains global variables from the database. This includes any variable relating to the unit, the station or the user.
When writing the query select a value or variable from the drop-down lists to enter it at the location of the cursor.
Click Execute Query to view the results of the query.
If the query is not valid an error will appear. Check that all table and parameter names are correct.
Using the example above will produce a result similar to the following.
The tabs in the lower half of the popup give the following information:
Variables: Lists all variables included in the query. Enter values for the variables and click Execute Query to test the query.
Results: Displays the results returned from the database.
Properties: Lets you select the value that will be returned when an item is selected in the control (ValueMember) and the values that will be displayed in the control (DisplayMember).
The most important information here is the ValueMember as this is the output of the control. This value will either be used to filter other controls or will be used in a stored procedure.
Click OK to save the control.
If the output of the control is going to be used somewhere else then it's a good idea to rename the control to something more recognisable (e.g., Product Code or Serial Number). The name cannot contain spaces.
If a function such as min, max, count, avg or a user defined function is used in an SQL query, a column name should be specified. For example:
select max(actual_date) as [actual date]
from tracking_point_passes
where tracking_point_id = 2
Also, note the square brackets used in the above example. Square brackets are required if there are spaces or other special characters included in the column name.
Edit Auto Selected Value
A value returned from the query used in Edit Query can select the default value that appears in the chosen control using the Edit Auto Selected Value feature.
The result specified must be from the ValueMember property. To set the default value:
Click the Properties tab. This will open up a list of options.
In the ATS Inspect category, select the AutoSelectedValue row and click the ellipses button.
This opens the AutoSelectValue popup. Enter a value that is a valid value for the valuemember in the large text box.
The auto selected values is not an SQL query but rather the value of a control or a run-time variable. Alternatively, a hardcoded string could be used that specifies either the value of a control or run-time variable or both.
Press OK.
Example
A Create New Unit Form that features a list control may utilise a query to return all product groups. In this example, the Automotive product group is required to be the default in the form.
A Create Unit form is created and the Generic Listbox control is added to the form. The Query function is selected and the following query is used:
SELECT pg.product_group_id AS [Product Group Id]
,x.m.value( '@Text[1]', 'varchar(max)' ) AS [Product Group]
FROM [product_groups] AS pg
cross apply pg.descriptions.nodes( '/Text/LanguageIdText' ) x(m)
WHERE x.m.value('@LanguageId[1]', 'int' ) = 1033
and pg.is_active = 1
order by [Product Group] asc
The Execute Query button returns the following results in the Results pane:
The Properties tab confirms that the valuemember is Product Group Id, which means that the value should be taken from this column. In this example, the value is 17.
This value, 17 in this example, can then be used in the AutoSelectValue dialog box to automatically select Automotive using the Edit Auto Selected Value function.
In Data Collect, the Create New Unit form displays the Automotive product group by default.
